﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Data.OleDb;
using System.IO;
using System.Text;
using System.Threading;
using System.Web;
using System.Web.UI;
using CLFramework.Globalization;

namespace CLFramework.Data
{
    /// <summary>
    /// Excel导出工具类
    /// </summary>
    public static class ExcelUtility
    {
        #region DataTableToTextWriter

        /// <summary>
        /// 从DataTable中导出Excel格式数据到TextWriter。
        /// </summary>
        /// <param name="source">The source.</param>
        /// <param name="txtWriter">The TextWriter.</param>
        /// <param name="resource">资源变量，栏位的名称等将通过此变量取得资源。</param>
        public static void DataTableToTextWriter(DataTable source, TextWriter txtWriter, IResource resource)
        {
            DataTableToTextWriter(source, txtWriter, resource, null, true);
        }

        /// <summary>
        /// 从DataTable中导出Excel格式数据到TextWriter。
        /// </summary>
        /// <param name="source">The source.</param>
        /// <param name="txtWriter">The TextWriter.</param>
        /// <param name="resource">资源变量，栏位的名称等将通过此变量取得资源。</param>
        /// <param name="columns">要显示的栏位集合，以逗号分隔，如"C1,C2,C3"</param>
        /// <param name="encodeExportItem">是否Encode导出项内容</param>
        public static void DataTableToTextWriter(DataTable source, TextWriter txtWriter, IResource resource,
                                               string columns, bool encodeExportItem)
        {
            DataTableToTextWriter(source, txtWriter, resource, columns, encodeExportItem, null);
        }

        /// <summary>
        /// 从Dataset中导出Excel格式数据到TextWriter。
        /// </summary>
        /// <param name="source">The source.</param>
        /// <param name="txtWriter">The TextWriter.</param>
        /// <param name="resource">资源变量，栏位的名称等将通过此变量取得资源。</param>
        /// <param name="columns">要显示的栏位集合，以逗号分隔，如"C1,C2,C3"</param>
        /// <param name="encodeExportItem">是否Encode导出项内容</param>
        /// <param name="floatFormatString">浮点型的格式化字串,默认为0.0000</param>
        public static void DataSetToTextWriter(DataSet source, TextWriter txtWriter, IResource resource,
                                               string columns, bool encodeExportItem, string floatFormatString)
        {
            //如果数据集为空则直接退出
            if (Checker.IsEmpty(source))
            {
                return;
            }
            DataTableToTextWriter(source.Tables[0], txtWriter, resource,
                columns, encodeExportItem, floatFormatString);
        }

        /// <summary>
        /// 从DataTable中导出Excel格式数据到TextWriter。
        /// </summary>
        /// <param name="source">The source.</param>
        /// <param name="txtWriter">The TextWriter.</param>
        /// <param name="resource">资源变量，栏位的名称等将通过此变量取得资源。</param>
        /// <param name="columns">要显示的栏位集合，以逗号分隔，如"C1,C2,C3"</param>
        /// <param name="encodeExportItem">是否Encode导出项内容</param>
        /// <param name="floatFormatString">浮点型的格式化字串,默认为0.0000</param>
        public static void DataTableToTextWriter(DataTable source, TextWriter txtWriter, IResource resource,
                                               string columns, bool encodeExportItem, string floatFormatString)
        {
            //如果数据集为空则直接退出
            if (Checker.IsEmpty(source))
            {
                return;
            }

            if (string.IsNullOrEmpty(floatFormatString))
            {
                floatFormatString = "0.0000";
            }
            //写入表头
            txtWriter.Write(excelHeader.Replace("##FloatFormat##", floatFormatString));
            txtWriter.Write(sheetHeader);
            txtWriter.Write(rowHeader);

            string[] columnArray;
            string[] columnText;
            int columnCount = source.Columns.Count;
            //显示列变量为空，则赋予所有列
            if (Checker.IsEmpty(columns))
            {
                columnArray = new string[columnCount];
                columnText = new string[columnCount];
                for (int i = 0; i < columnCount; i++)
                {
                    columnArray[i] = source.Columns[i].ColumnName;
                }
            }
            else
            {
                string[] tempArray = columns.Split(',');
                columnCount = tempArray.Length;
                columnArray = new string[columnCount];
                columnText = new string[columnCount];
                for (int i = 0; i < columnCount; i++)
                {
                    string[] tmp2 = tempArray[i].Split('*');
                    columnArray[i] = tmp2[0].Trim();
                    if (tmp2.Length > 1)
                    {
                        columnText[i] = tmp2[1].Trim();
                    }
                }
            }
            //写入列标题
            for (int i = 0; i < columnCount; i++)
            {
                string columnName;
                if (string.IsNullOrEmpty(columnText[i]))
                {
                    columnName = columnArray[i];
                    if (resource != null)
                    {
                        columnName = resource.GetString(columnName);
                    }
                }
                else
                {
                    columnName = HttpUtility.UrlDecode(columnText[i]);
                }
                txtWriter.Write(excelCell, "BoldColumn", columnName);
            }
            //写入注释标题
            string exportRemark = source.ExtendedProperties["ExportRemark"] as string;
            if (!string.IsNullOrEmpty(exportRemark))
            {
                string remarkCaption = "Remark";
                if (resource != null)
                {
                    remarkCaption = resource.GetString(remarkCaption);
                }
                txtWriter.Write(remarkCellCaption, remarkCaption);
            }
            txtWriter.Write(rowTail);
            //写入所有数据
            string temp;
            object rowValue;
            bool firstRow = true;
            DataView rows = source.DefaultView;
            foreach (DataRowView row in rows)
            {
                txtWriter.Write(rowHeader);
                for (int i = 0; i < columnCount; i++)
                {
                    rowValue = row[columnArray[i]];
                    switch (rowValue.GetType().FullName)
                    {
                        case "System.String":
                            temp = rowValue.ToString();
                            if (encodeExportItem)
                            {
                                temp = HttpUtility.HtmlAttributeEncode(temp);
                            }
                            txtWriter.Write(excelCell, "StringLiteral", temp);
                            break;
                        case "System.DateTime":
                            DateTime dateTime = (DateTime)rowValue;
                            txtWriter.Write(
                                excelCell, "DateLiteral", dateTime.ToString("yyyy-MM-dd HH:mm:ss"));
                            break;
                        case "System.Boolean":
                            txtWriter.Write(excelCell, "StringLiteral", rowValue);
                            break;
                        case "System.Int16":
                        case "System.Int32":
                        case "System.UInt16":
                        case "System.UInt32":
                        case "System.Byte":
                            txtWriter.Write(excelCell, "Integer", rowValue);
                            break;
                        case "System.Int64":
                        case "System.UInt64":
                            temp = rowValue.ToString();
                            //excel中只能显示15位数字,15位之后转化为字符串
                            if (temp.Length > 15)
                            {
                                txtWriter.Write(excelCell, "StringLiteral", temp);
                            }
                            else
                            {
                                txtWriter.Write(excelCell, "Integer", temp);
                            }
                            break;
                        case "System.Decimal":
                            temp = rowValue.ToString();
                            //判断是否小数
                            if (temp.Contains("."))
                            {
                                txtWriter.Write(excelCell, "Float", temp);
                            }
                            else
                            {
                                //excel中只能显示15位数字,15位之后转化为字符串
                                if (temp.Length > 15)
                                {
                                    txtWriter.Write(excelCell, "StringLiteral", temp);
                                }
                                else
                                {
                                    txtWriter.Write(excelCell, "Integer", temp);
                                }
                            }
                            break;
                        case "System.Double":
                            txtWriter.Write(excelCell, "Float", rowValue);
                            break;
                        case "System.DBNull":
                            txtWriter.Write(excelCell, "StringLiteral", string.Empty);
                            break;
                        default:
                            txtWriter.Write(excelCell, "StringLiteral", rowValue);
                            break;
                    }
                }
                //如果是第一列则显示注释
                if (firstRow)
                {
                    firstRow = false;
                    if (!string.IsNullOrEmpty(exportRemark))
                    {
                        txtWriter.Write(remarkCell, exportRemark);
                    }
                }
                txtWriter.Write(rowTail);
            }
            txtWriter.Write(sheetTail);
            txtWriter.Write(excelTail);
        }

        #region const variable

        #region excelHeader

        private const string excelHeader =
            @"<html xmlns:o=""urn:schemas-microsoft-com:office:office"" xmlns:x=""urn:schemas-microsoft-com:office:excel""
    xmlns:ss=""urn:schemas-microsoft-com:office:spreadsheet"" xmlns:html=""http://www.w3.org/TR/REC-html40""
    xmlns=""http://www.w3.org/TR/REC-html40"">
<head>
    <meta http-equiv=""Content-Type"" content=""text/html; charset=utf-8"">
    <meta name=""ProgId"" content=""Excel.Sheet"">
    <style>
<!--table
	{mso-displayed-decimal-separator:""\."";
	mso-displayed-thousand-separator:""\,"";}
@page
	{margin:1.0in .75in 1.0in .75in;
	mso-header-margin:.5in;
	mso-footer-margin:.5in;}
tr
	{mso-height-source:auto;
	mso-ruby-visibility:none;}
col
	{mso-width-source:auto;
	mso-ruby-visibility:none;}
br
	{mso-data-placement:same-cell;}
.style0
	{mso-number-format:General;
	text-align:general;
	vertical-align:bottom;
	white-space:nowrap;
	mso-rotate:0;
	mso-background-source:auto;
	mso-pattern:auto;
	color:windowtext;
	font-size:10.0pt;
	font-weight:400;
	font-style:normal;
	text-decoration:none;
	font-family:Arial, sans-serif;
	mso-font-charset:0;
	border:none;
	mso-protection:locked visible;
	mso-style-name:Normal;
	mso-style-id:0;}
td
	{mso-style-parent:style0;
	padding-top:1px;
	padding-right:1px;
	padding-left:1px;
	mso-ignore:padding;
	color:windowtext;
	font-size:10.0pt;
	font-weight:400;
	font-style:normal;
	text-decoration:none;
	font-family:Arial, sans-serif;
	mso-font-charset:0;
	mso-number-format:General;
	text-align:general;
	vertical-align:bottom;
	border:none;
	mso-background-source:auto;
	mso-pattern:auto;
	mso-protection:locked visible;
	white-space:nowrap;
	mso-rotate:0;}
.BoldColumn
	{mso-style-parent:style0;
	color:white;
	font-weight:700;
	text-align:center;
	vertical-align:middle;
	border-top:.5pt solid windowtext;
	border-right:.5pt solid windowtext;
	border-bottom:.5pt solid windowtext;
	border-left:none;
	background:#33CCCC;
	mso-pattern:auto none;}
.StringLiteral
	{mso-style-parent:style0;
	mso-number-format:""\@"";}
.Float
	{mso-style-parent:style0;
	mso-number-format:##FloatFormat##;}
.Integer
	{mso-style-parent:style0;
	mso-number-format:0;}
.DateLiteral
	{mso-style-parent:style0;
	mso-number-format:""yyyy-m-d h:mm:ss"";}
.RemarkCellCaption
	{mso-style-parent:style0;
	color:white;
	font-weight:700;
	mso-generic-font-family:auto;
	mso-font-charset:134;
	text-align:center;
	vertical-align:top;
	border-top:.5pt solid windowtext;
	border-right:.5pt solid windowtext;
	border-bottom:.5pt solid windowtext;
	border-left:.5pt solid windowtext;
	background:#99CCFF;
	mso-pattern:auto none;
	white-space:normal;}
.RemarkCell
	{mso-style-parent:style0;
	text-align:left;
	vertical-align:top;
	border-top:.5pt solid windowtext;
	border-right:.5pt solid windowtext;
	border-bottom:.5pt solid windowtext;
	border-left:.5pt solid windowtext;
	background:#FFFFE1;
	mso-pattern:auto none;
	white-space:normal;}
ruby
	{ruby-align:left;}
rt
	{color:windowtext;
	font-size:9.0pt;
	font-weight:400;
	font-style:normal;
	text-decoration:none;
	mso-generic-font-family:auto;
	mso-font-charset:134;
	mso-char-type:none;
	display:none;}
-->
</style>
    <!--[if gte mso 9]><xml>
 <x:ExcelWorkbook>
  <x:ExcelWorksheets>
   <x:ExcelWorksheet>
    <x:Name>Sheet1</x:Name>
    <x:WorksheetOptions>
     <x:Selected/>
     <x:ProtectContents>False</x:ProtectContents>
     <x:ProtectObjects>False</x:ProtectObjects>
     <x:ProtectScenarios>False</x:ProtectScenarios>
    </x:WorksheetOptions>
   </x:ExcelWorksheet>
  </x:ExcelWorksheets>
  <x:WindowHeight>10005</x:WindowHeight>
  <x:WindowWidth>10005</x:WindowWidth>
  <x:WindowTopX>120</x:WindowTopX>
  <x:WindowTopY>135</x:WindowTopY>
  <x:ProtectStructure>False</x:ProtectStructure>
  <x:ProtectWindows>False</x:ProtectWindows>
 </x:ExcelWorkbook>
</xml><![endif]-->
</head>
<body>";

        #endregion

        private const string excelCell = "<td class=\"{0}\">{1}</td>";
        private const string excelTail = "</body></html>";
        private const string remarkCell = @"
  <td></td>
  <td colspan=3 rowspan=48 class=""RemarkCell"">{0}</td>
";

        private const string remarkCellCaption =
            @"
  <td></td>
  <td colspan=3 class=""RemarkCellCaption"">{0}</td>
";

        private const string rowHeader = "<tr>";
        private const string rowTail = "</tr>";
        private const string sheetHeader = "<table>";
        private const string sheetTail = "</table>";

        #endregion

        #endregion

        #region DataTableToExcel

        /// <summary>
        /// 导出DataTable数据到指定的文件中。
        /// </summary>
        /// <param name="source">The source.</param>
        /// <param name="fileName">Name of the file.</param>
        public static void DataTableToExcel(DataTable source, string fileName)
        {
            DataTableToExcel(source, fileName, null);
        }

        /// <summary>
        /// 导出DataTable数据到指定的文件中。
        /// </summary>
        /// <param name="source">The source.</param>
        /// <param name="fileName">Name of the file.</param>
        /// <param name="resource">资源变量，栏位的名称等将通过此变量取得资源。</param>
        public static void DataTableToExcel(DataTable source, string fileName, IResource resource)
        {
            DataTableToExcel(source, fileName, resource, null, true);
        }

        /// <summary>
        /// 导出DataTable数据到临时文件中并返回临时文件的完整路径。
        /// </summary>
        /// <param name="source">The source.</param>
        /// <param name="resource">资源变量，栏位的名称等将通过此变量取得资源。</param>
        /// <param name="columns">要显示的栏位集合，以逗号分隔，如"C1,C2,C3"</param>
        /// <param name="encodeExportItem">是否Encode导出项内容</param>
        public static string DataTableToExcel(DataTable source, IResource resource, string columns, bool encodeExportItem)
        {
            return DataTableToExcel(source, resource, columns, encodeExportItem, null);
        }

        /// <summary>
        /// 导出DataTable数据到临时文件中并返回临时文件的完整路径。
        /// </summary>
        /// <param name="source">The source.</param>
        /// <param name="resource">资源变量，栏位的名称等将通过此变量取得资源。</param>
        /// <param name="columns">要显示的栏位集合，以逗号分隔，如"C1,C2,C3"</param>
        /// <param name="encodeExportItem">是否Encode导出项内容</param>
        /// <param name="floatFormatString">浮点型的格式化字串,默认为0.0000</param>
        public static string DataTableToExcel(DataTable source, IResource resource, string columns,
                                            bool encodeExportItem, string floatFormatString)
        {
            string tempFilePath = Path.GetTempFileName();
            DataTableToExcel(source, tempFilePath, resource, columns, encodeExportItem, floatFormatString);
            return tempFilePath;
        }

        /// <summary>
        /// 导出DataTable数据到指定的文件中。
        /// </summary>
        /// <param name="source">The source.</param>
        /// <param name="fileName">Name of the file.</param>
        /// <param name="resource">资源变量，栏位的名称等将通过此变量取得资源。</param>
        /// <param name="columns">要显示的栏位集合，以逗号分隔，如"C1,C2,C3"</param>
        /// <param name="encodeExportItem">是否Encode导出项内容</param>
        public static void DataTableToExcel(DataTable source, string fileName, IResource resource,
                                          string columns, bool encodeExportItem)
        {
            DataTableToExcel(source, fileName, resource, columns, encodeExportItem, null);
        }

        /// <summary>
        /// 导出DataTable数据到指定的文件中。
        /// </summary>
        /// <param name="source">The source.</param>
        /// <param name="fileName">Name of the file.</param>
        /// <param name="resource">资源变量，栏位的名称等将通过此变量取得资源。</param>
        /// <param name="columns">要显示的栏位集合，以逗号分隔，如"C1,C2,C3"</param>
        /// <param name="encodeExportItem">是否Encode导出项内容</param>
        /// <param name="floatFormatString">浮点型的格式化字串,默认为0.0000</param>
        public static void DataTableToExcel(DataTable source, string fileName, IResource resource,
                                          string columns, bool encodeExportItem, string floatFormatString)
        {
            using (StreamWriter excelDoc = new StreamWriter(fileName))
            {
                DataTableToTextWriter(source, excelDoc, resource, columns, encodeExportItem, floatFormatString);
            }
        }

        #endregion

        #region DataTableToExcelForWeb

        /// <summary>
        /// 导出DataTable数据到指定文件中。先写到磁盘再下载，占用内存较少，数据量小的情况下速度稍慢。
        /// </summary>
        /// <param name="source">The source.</param>
        /// <param name="fileName">Name of the file.</param>
        public static void DataTableToExcelForWeb(DataTable source, string fileName)
        {
            DataTableToExcelForWeb(source, fileName, null);
        }

        /// <summary>
        /// 导出DataTable数据到指定文件中。先写到磁盘再下载，占用内存较少，数据量小的情况下速度稍慢。
        /// </summary>
        /// <param name="source">The source.</param>
        /// <param name="fileName">Name of the file.</param>
        /// <param name="resource">资源变量，栏位的名称等将通过此变量取得资源。</param>
        public static void DataTableToExcelForWeb(DataTable source, string fileName, IResource resource)
        {
            DataTableToExcelForWeb(source, fileName, resource, null);
        }

        /// <summary>
        /// 导出DataTable数据到指定文件中。先写到磁盘再下载，占用内存较少，数据量小的情况下速度稍慢。
        /// </summary>
        /// <param name="source">The source.</param>
        /// <param name="fileName">Name of the file.</param>
        /// <param name="resource">资源变量，栏位的名称等将通过此变量取得资源。</param>
        /// <param name="columns">要显示的栏位集合，以逗号分隔，如"C1,C2,C3"</param>
        public static void DataTableToExcelForWeb(DataTable source, string fileName, IResource resource,
                                                string columns)
        {
            DataTableToExcelForWeb(source, fileName, resource, columns, true);
        }

        /// <summary>
        /// 导出DataTable数据到指定文件中。先写到磁盘再下载，占用内存较少，数据量小的情况下速度稍慢。
        /// </summary>
        /// <param name="source">The source.</param>
        /// <param name="fileName">Name of the file.</param>
        /// <param name="resource">资源变量，栏位的名称等将通过此变量取得资源。</param>
        /// <param name="columns">要显示的栏位集合，以逗号分隔，如"C1,C2,C3"</param>
        /// <param name="encodeExportItem">是否Encode导出项内容</param>
        public static void DataTableToExcelForWeb(DataTable source, string fileName, IResource resource,
                                                string columns, bool encodeExportItem)
        {
            DataTableToExcelForWeb(source, fileName, resource, columns, encodeExportItem, null);
        }

        /// <summary>
        /// 导出DataTable数据到指定文件中。先写到磁盘再下载，占用内存较少，数据量小的情况下速度稍慢。
        /// </summary>
        /// <param name="source">The source.</param>
        /// <param name="fileName">Name of the file.</param>
        /// <param name="resource">资源变量，栏位的名称等将通过此变量取得资源。</param>
        /// <param name="columns">要显示的栏位集合，以逗号分隔，如"C1,C2,C3"</param>
        /// <param name="encodeExportItem">是否Encode导出项内容</param>
        /// <param name="floatFormatString">浮点型的格式化字串,默认为0.0000</param>
        public static void DataTableToExcelForWeb(DataTable source, string fileName, IResource resource,
                                                string columns, bool encodeExportItem, string floatFormatString)
        {
            //写到服务器临时文件
            string tempFilePath = DataTableToExcel(source, resource, columns, encodeExportItem, floatFormatString);
            //写到http，开始下载
            TransmitFile(tempFilePath, fileName);
        }

        #endregion

        #region DataTableToExcelForWeb2

        /// <summary>
        /// 导出DataTable数据到指定文件中。直接写到页面输出，占用内存较大，数据量小的情况下速度稍快。
        /// </summary>
        /// <param name="source">The source.</param>
        /// <param name="fileName">Name of the file.</param>
        public static void DataTableToExcelForWeb2(DataTable source, string fileName)
        {
            DataTableToExcelForWeb2(source, fileName, null);
        }

        /// <summary>
        /// 导出DataTable数据到指定文件中。直接写到页面输出，占用内存较大，数据量小的情况下速度稍快。
        /// </summary>
        /// <param name="source">The source.</param>
        /// <param name="fileName">Name of the file.</param>
        /// <param name="resource">资源变量，栏位的名称等将通过此变量取得资源。</param>
        public static void DataTableToExcelForWeb2(DataTable source, string fileName, IResource resource)
        {
            DataTableToExcelForWeb2(source, fileName, resource, null);
        }

        /// <summary>
        /// 导出DataTable数据到指定文件中。直接写到页面输出，占用内存较大，数据量小的情况下速度稍快。
        /// </summary>
        /// <param name="source">The source.</param>
        /// <param name="fileName">Name of the file.</param>
        /// <param name="resource">资源变量，栏位的名称等将通过此变量取得资源。</param>
        /// <param name="columns">要显示的栏位集合，以逗号分隔，如"C1,C2,C3"</param>
        public static void DataTableToExcelForWeb2(DataTable source, string fileName, IResource resource, string columns)
        {
            DataTableToExcelForWeb2(source, fileName, resource, columns, null);
        }

        /// <summary>
        /// 导出DataTable数据到指定文件中。直接写到页面输出，占用内存较大，数据量小的情况下速度稍快。
        /// </summary>
        /// <param name="source">The source.</param>
        /// <param name="fileName">Name of the file.</param>
        /// <param name="resource">资源变量，栏位的名称等将通过此变量取得资源。</param>
        /// <param name="columns">要显示的栏位集合，以逗号分隔，如"C1,C2,C3"</param>
        /// <param name="floatFormatString">浮点型的格式化字串,默认为0.0000</param>
        public static void DataTableToExcelForWeb2(DataTable source, string fileName, IResource resource, string columns, string floatFormatString)
        {
            string encodeFileName = HttpUtility.UrlEncode(fileName);
            HttpContext.Current.Response.Clear();
            HttpContext.Current.Response.ContentType = "application/ms-excel";
            HttpContext.Current.Response.ContentEncoding = Encoding.GetEncoding("UTF-8");
            HttpContext.Current.Response.AppendHeader("Content-Disposition", "attachment;filename=" + encodeFileName);

            DataTableToTextWriter(source, HttpContext.Current.Response.Output, resource, columns, true, floatFormatString);

            HttpContext.Current.Response.End();
        }

        #endregion

        #region DataSetToExcelForWeb2

        /// <summary>
        /// 导出DataSet数据到指定文件中。直接写到页面输出，占用内存较大，数据量小的情况下速度稍快。
        /// </summary>
        /// <param name="source">The source.</param>
        /// <param name="fileName">Name of the file.</param>
        public static void DataSetToExcelForWeb2(DataSet source, string fileName)
        {
            DataSetToExcelForWeb2(source, fileName, null);
        }

        /// <summary>
        /// 导出DataSet数据到指定文件中。直接写到页面输出，占用内存较大，数据量小的情况下速度稍快。
        /// </summary>
        /// <param name="source">The source.</param>
        /// <param name="fileName">Name of the file.</param>
        /// <param name="resource">资源变量，栏位的名称等将通过此变量取得资源。</param>
        public static void DataSetToExcelForWeb2(DataSet source, string fileName, IResource resource)
        {
            DataSetToExcelForWeb2(source, fileName, resource, null);
        }

        /// <summary>
        /// 导出DataSet数据到指定文件中。直接写到页面输出，占用内存较大，数据量小的情况下速度稍快。
        /// </summary>
        /// <param name="source">The source.</param>
        /// <param name="fileName">Name of the file.</param>
        /// <param name="resource">资源变量，栏位的名称等将通过此变量取得资源。</param>
        /// <param name="columns">要显示的栏位集合，以逗号分隔，如"C1,C2,C3"</param>
        public static void DataSetToExcelForWeb2(DataSet source, string fileName, IResource resource, string columns)
        {
            DataSetToExcelForWeb2(source, fileName, resource, columns, null);
        }

        /// <summary>
        /// 导出DataSet数据到指定文件中。直接写到页面输出，占用内存较大，数据量小的情况下速度稍快。
        /// </summary>
        /// <param name="source">The source.</param>
        /// <param name="fileName">Name of the file.</param>
        /// <param name="resource">资源变量，栏位的名称等将通过此变量取得资源。</param>
        /// <param name="columns">要显示的栏位集合，以逗号分隔，如"C1,C2,C3"</param>
        /// <param name="floatFormatString">浮点型的格式化字串,默认为0.0000</param>
        public static void DataSetToExcelForWeb2(DataSet source, string fileName, IResource resource, string columns, string floatFormatString)
        {
            string encodeFileName = HttpUtility.UrlEncode(fileName);
            HttpContext.Current.Response.Clear();
            HttpContext.Current.Response.ContentType = "application/ms-excel";
            HttpContext.Current.Response.ContentEncoding = Encoding.GetEncoding("UTF-8");
            HttpContext.Current.Response.AppendHeader("Content-Disposition", "attachment;filename=" + encodeFileName);

            DataSetToTextWriter(source, HttpContext.Current.Response.Output, resource, columns, true, floatFormatString);

            HttpContext.Current.Response.End();
        }

        #endregion

        #region WebControlToExcel

        /// <summary>
        /// 导出Web控件中的数据到Excel文件中。
        /// </summary>
        /// <param name="ctl">要导出的Web控件</param>
        /// <param name="fileName">要导出的文件名</param>
        public static void WebControlToExcel(Control ctl, string fileName)
        {
            HttpContext.Current.Response.Clear();
            HttpContext.Current.Response.Charset = "";
            HttpContext.Current.Response.AppendHeader("Content-Disposition", "attachment;filename=" + fileName);
            HttpContext.Current.Response.ContentEncoding = Encoding.GetEncoding("utf-7");
            HttpContext.Current.Response.ContentType = "application/ms-excel";
            //application/ms-excel;image/JPEG;text/HTML;image/GIF;vnd.ms-excel/msword

            ctl.Page.EnableViewState = false;
            StringWriter tw = new StringWriter();
            HtmlTextWriter hw = new HtmlTextWriter(tw);

            ctl.RenderControl(hw);

            HttpContext.Current.Response.Write(tw.ToString());
            HttpContext.Current.Response.End();
        }

        #endregion

        #region TransferFile

        /// <summary>
        /// 将Web服务器上的临时文件传输到客户端，并删除临时文件。
        /// </summary>
        /// <param name="tempFilePath">存在语服务器上的源临时文件。</param>
        /// <param name="desFileName">保存的客户端的文件</param>
        public static void TransmitFile(string tempFilePath, string desFileName)
        {
            //写到http，开始下载
            string encodeFileName = HttpUtility.UrlEncode(desFileName);
            HttpContext.Current.Response.Clear();
            HttpContext.Current.Response.ContentType = "application/ms-excel";
            HttpContext.Current.Response.ContentEncoding = Encoding.GetEncoding("UTF-8");
            HttpContext.Current.Response.AppendHeader("Content-Disposition", "attachment;filename=" + encodeFileName);
            HttpContext.Current.Response.TransmitFile(tempFilePath);
            HttpContext.Current.Response.Flush();
            //启动线程清理临时文件
            DeleteTempFile(tempFilePath, false);
            HttpContext.Current.Response.End();
        }

        #endregion

        #region DeleteTempFile

        /// <summary>
        /// 启动线程清理临时文件，首次等50秒，如发现文件正在使用则此后每30秒尝试删除一次，
        /// 共尝试10次。
        /// </summary>
        /// <param name="tempFilePath">The temp file path.</param>
        /// <param name="deleteDirectory">是否删除所在文件夹</param>
        public static void DeleteTempFile(string tempFilePath, bool deleteDirectory)
        {
            if (!File.Exists(tempFilePath))
            {
                return;
            }
            Thread t = new Thread(delegate()
            {
                int retry = 0;
                int interval = 50000; //首次等待50秒
                while (retry < 12)
                {
                    try
                    {
                        Thread.Sleep(interval);
                        if (deleteDirectory)
                        {
                            string dir = Path.GetDirectoryName(tempFilePath);
                            Directory.Delete(dir, true);
                        }
                        else
                        {
                            File.Delete(tempFilePath);
                        }
                        break;
                    }
                    catch (IOException)
                    {
                        //删除失败后则将等待时间置为30秒
                        interval = 30000;
                        retry++;
                    }
                    catch (Exception)
                    {
                        break;
                    }
                }
            });
            t.Start();
        }

        #endregion

        #region ReadExcel

        private const string connectionStringFormatForRead =
            "Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties='Excel 8.0;HDR=YES;IMEX=1';";

        private const string connectionStringFormatForWrite =
            "Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties='Excel 8.0;HDR=YES;';";

        private static void ValidateExcelFile(string filePath)
        {
            string extension = Path.GetExtension(filePath);
            if (extension.ToLower() != ".xls")
            {
                throw new Exception("只支持Excel2000~2003文件。");
            }

            if (!File.Exists(filePath))
            {
                throw new FileNotFoundException("Excel文件不存在。", filePath);
            }
        }

        private static string GetSheetName(string sheetName)
        {
            if (string.IsNullOrEmpty(sheetName))
            {
                throw new Exception("Sheet名称不能为空。");
            }
            if (!sheetName.EndsWith("$"))
            {
                sheetName += "$";
            }
            return sheetName;
        }

        /// <summary>
        /// 取得指定Excel文件中的所有Sheet的名字。
        /// </summary>
        /// <param name="filePath">Excel文件路径。</param>
        /// <returns></returns>
        public static IList<string> GetAllSheetNames(string filePath)
        {
            ValidateExcelFile(filePath);

            string connectionString = string.Format(connectionStringFormatForRead, filePath);
            using (OleDbConnection connection = new OleDbConnection(connectionString))
            {
                connection.Open();
                return GetAllSheetNames(connection);
            }
        }

        /// <summary>
        /// 取得指定Excel文件中的所有Sheet的名字。
        /// </summary>
        /// <param name="connection">到Excel文件的连接.</param>
        /// <returns></returns>
        private static IList<string> GetAllSheetNames(OleDbConnection connection)
        {
            IList<string> sheetNames = new List<string>();
            //取得所有表                    
            DataTable tablesInfo = connection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
            if (tablesInfo != null && tablesInfo.Rows.Count > 0)
            {
                foreach (DataRow row in tablesInfo.Rows)
                {
                    sheetNames.Add(row["TABLE_NAME"].ToString());
                }
            }
            return sheetNames;
        }

        /// <summary>
        /// Reads the sheets.
        /// </summary>
        /// <param name="filePath">The file path.</param>
        /// <param name="sheetNames">The sheet names.</param>
        /// <returns></returns>
        public static DataSet ReadSheets(string filePath, params string[] sheetNames)
        {
            ValidateExcelFile(filePath);

            DataSet result = new DataSet();
            string connectionString = string.Format(connectionStringFormatForRead, filePath);
            using (OleDbConnection connection = new OleDbConnection(connectionString))
            {
                connection.Open();
                IList<string> selectedSheets;
                if (sheetNames == null || sheetNames.Length == 0)
                {
                    selectedSheets = GetAllSheetNames(connection);
                }
                else
                {
                    selectedSheets = sheetNames;
                }
                foreach (string sheet in selectedSheets)
                {
                    result.Tables.Add(ReadSheet(connection, sheet));
                }
            }
            return result;
        }

        /// <summary>
        /// 读取Excel文件中第一个sheet的内容。
        /// </summary>
        /// <param name="filePath">The file path.</param>
        /// <returns></returns>
        public static DataTable ReadFirstSheet(string filePath)
        {
            ValidateExcelFile(filePath);
            string connectionString = string.Format(connectionStringFormatForRead, filePath);
            using (OleDbConnection connection = new OleDbConnection(connectionString))
            {
                connection.Open();
                IList<string> sheets = GetAllSheetNames(connection);
                if (sheets.Count > 0)
                {
                    string firstSheetName = sheets[0];
                    return ReadSheet(connection, firstSheetName);
                }
            }
            return null;
        }

        /// <summary>
        /// Reads the sheet.
        /// </summary>
        /// <param name="connection">The connection.</param>
        /// <param name="sheetName">Name of the sheet.</param>
        /// <returns></returns>
        private static DataTable ReadSheet(OleDbConnection connection, string sheetName)
        {
            OleDbDataAdapter adapter = GetAdapter(sheetName, connection);
            DataTable result = new DataTable(sheetName);
            adapter.Fill(result);
            return result;
        }

        /// <summary>
        /// Gets the adapter.
        /// </summary>
        /// <param name="sheetName">Name of the sheet.</param>
        /// <param name="connection">The connection.</param>
        /// <returns></returns>
        private static OleDbDataAdapter GetAdapter(string sheetName, OleDbConnection connection)
        {
            sheetName = GetSheetName(sheetName);
            string sql = string.Format(@"select * from [{0}]", sheetName);
            OleDbCommand command = new OleDbCommand(sql, connection);
            return new OleDbDataAdapter(command);
        }

        /// <summary>
        /// Reads the sheet.
        /// </summary>
        /// <param name="filePath">The file path.</param>
        /// <param name="sheetName">Name of the sheet.</param>
        /// <returns></returns>
        public static DataTable ReadSheet(string filePath, string sheetName)
        {
            ValidateExcelFile(filePath);
            string connectionString = string.Format(connectionStringFormatForRead, filePath);
            using (OleDbConnection connection = new OleDbConnection(connectionString))
            {
                connection.Open();
                return ReadSheet(connection, sheetName);
            }
        }

        #endregion

        #region ReadTextExcel

        /// <summary>
        /// 读取文本Excel并以指定的强类型DataTable返回。如果出现重复列将会在结尾加上列索引。
        /// </summary>
        /// <typeparam name="T">强类型DataSet</typeparam>
        /// <param name="filePath">文件路径</param>
        /// <param name="headerRowIndex">标题行索引</param>
        /// <param name="dataRowBegin">数据行起始索引</param>
        /// <param name="headerColumnBegin">标题行起始列索引</param>
        /// <param name="headerColumnEnd">标题行结束列索引</param>
        /// <param name="columnMapping">标题行名称与数据列的对应集合</param>
        /// <param name="splitChar">分隔符</param>
        /// <returns></returns>
        public static T ReadTextExcelAsTypedDataTable<T>(string filePath, int headerRowIndex, int dataRowBegin,
            int headerColumnBegin, int headerColumnEnd, DataColumnMappingCollection columnMapping, char splitChar)
            where T : DataTable, new()
        {
            DataTable originalTable = ReadTextExcel(filePath, headerRowIndex, dataRowBegin,
                headerColumnBegin, headerColumnEnd, splitChar);
            T result = new T();
            foreach (DataRow row in originalTable.Rows)
            {
                DataRow newRow = result.NewRow();
                foreach (DataColumnMapping mapping in columnMapping)
                {
                    newRow[mapping.DataSetColumn] = row[mapping.SourceColumn];
                }
                result.Rows.Add(newRow);
            }
            return result;
        }

        /// <summary>
        /// 读取文本型Excel, 如果出现重复列将会在结尾加上列索引。
        /// </summary>
        /// <param name="filePath">文件路径</param>
        /// <param name="headerRowIndex">标题行索引</param>
        /// <param name="dataRowBegin">数据行起始索引</param>
        /// <param name="headerColumnBegin">标题行起始列索引</param>
        /// <param name="headerColumnEnd">标题行结束列索引</param>
        /// <param name="splitChar">分隔符</param>
        /// <returns></returns>
        public static DataTable ReadTextExcel(string filePath, int headerRowIndex, int dataRowBegin,
            int headerColumnBegin, int headerColumnEnd, char splitChar)
        {
            if (!File.Exists(filePath))
            {
                throw new FileNotFoundException("文件不存在。");
            }

            int currentRow = 0;
            string currentRowText;
            DataTable excelTable = new DataTable();
            using (StreamReader stream = File.OpenText(filePath))
            {
                while (stream.Peek() > 0)
                {
                    currentRow += 1;
                    currentRowText = stream.ReadLine();
                    if (currentRow >= dataRowBegin)
                    {
                        //添加行
                        if (string.IsNullOrEmpty(currentRowText))
                        {
                            //遇到空行直接结束解析.
                            break;
                        }
                        string[] columns = currentRowText.Split(splitChar);
                        //数据列数不足
                        if (columns.Length < headerColumnEnd)
                        {
                            continue;
                        }
                        //读取数据
                        DataRow newRow = excelTable.NewRow();
                        for (int i = headerColumnBegin - 1; i <= headerColumnEnd - 1; i++)
                        {
                            int columnIndex = i - headerColumnBegin + 1;
                            newRow[columnIndex] = columns[i];
                        }
                        excelTable.Rows.Add(newRow);
                    }
                    else if (currentRow == headerRowIndex)
                    {
                        //添加栏位名
                        if (string.IsNullOrEmpty(currentRowText))
                        {
                            throw new FormatException("不能发现标题行。");
                        }
                        string[] columns = currentRowText.Split(splitChar);
                        //数据格式错误
                        if (columns.Length < headerColumnEnd)
                        {
                            throw new FormatException("文件格式错误。");
                        }
                        for (int i = headerColumnBegin - 1; i <= headerColumnEnd - 1; i++)
                        {
                            if (string.IsNullOrEmpty(columns[i]))
                            {
                                throw new FormatException("标题格式错误。");
                            }
                            string column = columns[i];
                            if (excelTable.Columns.Contains(column))
                            {
                                //发现重复列式末尾加上列索引
                                column += i + 1;
                            }
                            excelTable.Columns.Add(column.Trim());
                        }
                    }
                }
            }
            return excelTable;
        }

        #endregion

        #region AppendRowToSheet

        /// <summary>
        /// 添加一行.
        /// </summary>
        /// <param name="filePath">The file path.</param>
        /// <param name="sheetName">Name of the sheet.</param>
        /// <param name="rowDataArray">The row data array.</param>
        /// <returns></returns>
        public static int AppendRowToSheet(string filePath, string sheetName, params object[] rowDataArray)
        {
            ValidateExcelFile(filePath);
            string connectionString = string.Format(connectionStringFormatForWrite, filePath);
            using (OleDbConnection connection = new OleDbConnection(connectionString))
            {
                connection.Open();
                string parameters = "";
                foreach (object data in rowDataArray)
                {
                    parameters += string.Format("'{0}',", data);
                }
                if (parameters != "")
                {
                    parameters = parameters.Substring(0, parameters.Length - 1);
                }
                sheetName = GetSheetName(sheetName);
                string sql = string.Format(@"insert into [{0}] values ({1})", sheetName, parameters);
                OleDbCommand command = new OleDbCommand(sql, connection);
                return command.ExecuteNonQuery();
            }
        }

        #endregion

    }
}
